Imports System.Data.OleDb
Imports DTO

Public Class PhongDAO

    Inherits AbstractDAO

    Public Function Lay_Bang(ByVal chuoi_lenh As String) As DataTable
        Dim dt As New DataTable
        Dim chuoi_ket_noi As New OleDbConnection
        chuoi_ket_noi = AbstractDAO.ConnectionData()
        Dim bo_thich_ung As OleDbDataAdapter
        bo_thich_ung = New OleDbDataAdapter(chuoi_lenh, chuoi_ket_noi)
        bo_thich_ung.FillSchema(dt, SchemaType.Mapped)
        bo_thich_ung.Fill(dt)
        Return dt
    End Function

    Public Function Doc_Cau_Truc(ByVal chuoi_lenh As String) As DataTable
        Try
            Dim dt As New DataTable
            Dim chuoi_ket_noi As New OleDbConnection
            chuoi_ket_noi = AbstractDAO.ConnectionData()
            Dim da As New OleDbDataAdapter(chuoi_lenh, chuoi_ket_noi)
            da.FillSchema(dt, SchemaType.Mapped)
            Return dt
        Catch ex As Exception
            Return New DataTable()
        End Try
    End Function

    Public Function TimPhongTheoNhieuTieuChi(ByVal pCRT As PhongCRT) As DataTable
        Dim dt As New DataTable
        Dim cn As New OleDbConnection
        cn = AbstractDAO.ConnectionData()
        Dim strSQL As String
        strSQL = "select p.TenPhong, p.MaLoaiPhong, p.TinhTrangPhong from Phong p, LoaiPhong lp where p.MaLoaiPhong = lp.MaLoaiPhong"
        Dim cmb As New OleDbCommand()

        Dim strTen_Phong As String = "2=2"
        Dim strHien_Trang_Phong As String = "3=3"
        Dim strLoai_Phong As String = "4=4"

        If pCRT.CHK_TenPhong = True Then
            strTen_Phong = "p.TenPhong like ? "
            cmb.Parameters.Add("@p.TenPhong", OleDbType.WChar)
            cmb.Parameters("@p.TenPhong").Value = "%" + pCRT.TenPhong.ToString() + "%"
        End If
        If pCRT.CHK_LoaiPhong = True Then
            strLoai_Phong = "p.MaLoaiPhong = ?"
            cmb.Parameters.Add("@p.MaLoaiPhong", OleDbType.Integer)
            cmb.Parameters("@p.MaLoaiPhong").Value = pCRT.LoaiPhong
        End If
        If pCRT.CHK_HienTrangPhong = True Then
            strHien_Trang_Phong = "p.TinhTrangPhong like ? "
            cmb.Parameters.Add("@p.TinhTrangPhong", OleDbType.WChar)
            cmb.Parameters("@p.TinhTrangPhong").Value = "%" + pCRT.HienTrangPhong.ToString() + "%"
        End If
        strSQL += " and " + strTen_Phong + " and " + strLoai_Phong + " and " + strHien_Trang_Phong
        cmb.Connection = cn
        cmb.CommandText = strSQL
        Dim da As New OleDbDataAdapter(cmb)
        da.Fill(dt)
        Return dt
    End Function

    Public Sub ThemPhong(ByVal dr As DataRow)
        Dim cn As New OleDbConnection
        cn = AbstractDAO.ConnectionData()
        Dim strSQL As String
        strSQL = "insert into Phong(TenPhong, MaLoaiPhong, TinhTrangPhong) values(?,?,?)"
        Dim cmb As New OleDbCommand(strSQL, cn)
        cmb.Parameters.Add("@TenPhong", OleDbType.WChar)
        cmb.Parameters.Add("@MaLoaiPhong", OleDbType.Integer)
        cmb.Parameters.Add("@TinhTrangPhong", OleDbType.WChar)

        cmb.Parameters("@TenPhong").Value = dr("TenPhong")
        cmb.Parameters("@MaLoaiPhong").Value = dr("MaLoaiPhong")
        cmb.Parameters("@TinhTrangPhong").Value = dr("TinhTrangPhong")
        cmb.ExecuteNonQuery()
        cn.Close()
    End Sub

    Public Function LayBangPhong() As DataTable
        Dim dt As New DataTable
        Dim cn As New OleDbConnection
        cn = AbstractDAO.ConnectionData()
        Dim strSQL As String
        strSQL = "select p.MaPhong, p.TenPhong, p.MaLoaiPhong, p.TinhTrangPhong, lp.TenLoaiPhong, lp.DonGia, lp.SoKhachToiDa, lp.SoLuong from Phong p, LoaiPhong lp where p.MaLoaiPhong = lp.MaLoaiPhong group by p.MaPhong, p.TenPhong, p.MaLoaiPhong, p.TinhTrangPhong, lp.TenLoaiPhong, lp.DonGia, lp.SoKhachToiDa, lp.SoLuong"
        Dim da As OleDbDataAdapter
        da = New OleDbDataAdapter(strSQL, cn)
        da.Fill(dt)
        cn.Close()
        Return dt
    End Function

    Public Function LayThongTinPhong(ByVal maPhong As Integer) As PhongDTO
        Dim pDto As New PhongDTO()
        Dim cn As OleDbConnection
        cn = AbstractDAO.ConnectionData()
        Dim strSQL As String
        strSQL = "select p.MaPhong, p.TenPhong, p.MaLoaiPhong, p.TinhTrangPhong, lp.TenLoaiPhong, lp.DonGia, lp.SoKhachToiDa, lp.SoLuong from Phong p, LoaiPhong lp where p.MaLoaiPhong = lp.MaLoaiPhong and p.MaPhong = " + maPhong + " group by  p.MaPhong, p.TenPhong, p.MaLoaiPhong, p.TinhTrangPhong, lp.TenLoaiPhong, lp.DonGia, lp.SoKhachToiDa, lp.SoLuong"
        Dim cmb As New OleDbCommand(strSQL, cn)
        Dim dr As OleDbDataReader
        dr = cmb.ExecuteReader()
        While (dr.Read())
            pDto.MaPhong = dr("MaPhong")
            pDto.TenPhong = dr("TenPhong")
            pDto.MaLoaiPhong = dr("MaLoaiPhong")
            pDto.TinhTrang = dr("TinhTrangPhong")
            Dim lpDto As New LoaiPhongDTO()
            lpDto.TenLoaiPhong = dr("TenLoaiPhong")
            lpDto.DonGia = dr("DonGia")
            lpDto.SoLuong = dr("SoLuong")
            lpDto.SoKhachToiDa = dr("SoKhachToiDa")
            pDto.LoaiPhong = lpDto
        End While
        dr.Close()
        cn.Close()
        Return pDto
    End Function

    Public Sub CapNhapPhong(ByVal dr As DataRow)
        Dim cn As OleDbConnection
        cn = AbstractDAO.ConnectionData()
        Dim strSQL As String
        strSQL = "update Phong " & _
        "set TenPhong = ?, MaLoaiPhong = ?, TinhTrangPhong = ? " & _
        "where MaPhong = ? "
        Dim cmb As New OleDbCommand(strSQL, cn)

        cmb.Parameters.Add("@TenPhong", OleDbType.WChar)
        cmb.Parameters.Add("@MaLoaiPhong", OleDbType.Integer)
        cmb.Parameters.Add("@TinhTrangPhong", OleDbType.WChar)
        cmb.Parameters.Add("@MaPhong", OleDbType.Integer)

        cmb.Parameters("@TenPhong").Value = dr("TenPhong")
        cmb.Parameters("@MaLoaiPhong").Value = dr("MaLoaiPhong")
        cmb.Parameters("@TinhTrangPhong").Value = dr("TinhTrangPhong")
        cmb.Parameters("@MaPhong").Value = dr("MaPhong")

        cmb.ExecuteNonQuery()
        cn.Close()
    End Sub

    Public Sub XoaPhong(ByVal dr As DataRow)
        Dim cn As OleDbConnection
        cn = AbstractDAO.ConnectionData()
        Dim strSQL As String
        strSQL = "delete from Phong where MaPhong = ?"
        Dim cmb As New OleDbCommand(strSQL, cn)
        cmb.Parameters.Add("@MaPhong", OleDbType.Integer)
        cmb.Parameters("@MaPhong").Value = dr("MaPhong")
        cmb.ExecuteNonQuery()
        cn.Close()
    End Sub

End Class
